const connection = require("../app/database.js");
// 所有和数据库相关的操作
class journalServer {
    // 添加一条日记
    async addJournalServer(
        title,
        content,
        plan_status,
        mood,
        user_id,
        happen,
        weather
    ) {
        const statement = `INSERT INTO journal (user_id, journal_title, journal_content, journal_plan_status,mood ,happen , weather ) VALUES (?, ?, ?, ?, ? ,? , ?);`;
        const [result] = await connection.execute(statement, [
            user_id,
            title,
            content,
            plan_status,
            mood,
            happen,
            weather,
        ]);
        return result;
    }

    // 查询指定区间日记
    async queryJournalServer(user_id, startTime, endTime) {
        const statement = `SELECT 
			j.id id,j.user_id user_id , j.journal_title title , j.journal_content content , j.journal_plan_status plan_status , j.mood mood , j.updateAt updateTime, j.happen happen , j.weather weather,
			(SELECT 
				JSON_ARRAYAGG(JSON_OBJECT(
					"id" , p.id , "filename" , p.filename  , "user_id" , p.user_id
				))
			FROM photo p WHERE p.journal_id = j.id ) img_list
		FROM journal j
		WHERE j.user_id = ?
		AND j.updateAt BETWEEN ?  AND ?;`;

        const [result] = await connection.execute(statement, [
            user_id,
            startTime,
            endTime,
        ]);
        return result;
    }

    // 查询所有日记 分页
    async queryAllJournalServer(user_id, limit = 5, offset = 0) {
        const statement = `
        SELECT 
            j.id id,j.user_id user_id , j.journal_title title , j.journal_content content , j.journal_plan_status plan_status , j.mood mood , j.happen happen , j.weather weather , j.updateAt updateTime, j.createAt createTime 
        FROM journal j
        WHERE j.user_id = ? ORDER BY id DESC LIMIT ? OFFSET ? ;
        `;
        const [result] = await connection.execute(statement, [
            user_id,
            String(limit),
            String(offset),
        ]);
        return result;
    }

    // 更新日记
    async updateJournalServer(title, content, plan_status, mood,  happen, weather , journal_id) {
        const statement = `
        UPDATE journal SET journal_title = ? , journal_content = ? , journal_plan_status =? ,mood = ?  ,happen =?  , weather = ?  WHERE id = ?;
        `;
        const [result] = await connection.execute(statement, [
            title,
            content,
            plan_status,
            mood,
            happen,
            weather,
            journal_id,
        ]);
        return result;
    }
}
module.exports = new journalServer();
